/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



package com.myapp.struts.dao;

//~--- non-JDK imports --------------------------------------------------------

import com.myapp.struts.beans.ScheduleBean;
import com.myapp.struts.prepare.SchedulePrepareAL;

//~--- JDK imports ------------------------------------------------------------

import java.sql.*;

import java.util.*;

public class ScheduleDao
 {
    SchedulePrepareAL shpl = new SchedulePrepareAL();
    ArrayList         al, alt;
    private ArrayList alist;
    boolean           b1, b;
    Connection        con;    // get the connection object reference
    Connect           drv;    // get the driver object reference
    int               i, count, count1;
    PreparedStatement pstmt;
    ResultSet         rs;
    private String query;

//  method to establish a connection to Database
    public Connection getConnect() throws Exception {
        drv = new Connect();
        con = drv.mySql();

        return con;
    }    // end of getconnection()

    public boolean insert(ScheduleBean shb) throws Exception {
        try {
            con = getConnect();
            con.setAutoCommit(false);
            System.out.println("INSERT METHOD AT ScheduleDAO...");

            String ss = "INSERT INTO schedule VALUES(?,?,?,?,?,?,?,?)";

            pstmt = con.prepareStatement(ss);
            System.out.println("psmt" + pstmt);
            System.out.println("conn and stmt is created");

            String scheduleno   = shb.getSchedule_No();
            String schedulename = shb.getSchedule_Name();
            String sub1         = shb.getSub_Schedule1();
            String sub2         = shb.getSub_Schedule2();
            String desc         = shb.getDescription();
            String cby          = shb.getCreatedBy();
            String cdt          = shb.getCreatedDt();

            pstmt.setString(1, scheduleno);
            pstmt.setString(2, schedulename);
            pstmt.setString(3, sub1);
            pstmt.setString(4, sub2);
            pstmt.setString(5, desc);
            pstmt.setString(6, cby);
            pstmt.setString(7, cdt);
            pstmt.setString(8, "1");

            // System.out.println(code);
            i = pstmt.executeUpdate();
            con.commit();
            pstmt.close();
            con.close();

            if (i == 1) {
                System.out.println("inside SCHEDULEDAO values inserted");
                b = true;
            }        // end of if
                    else {
                System.out.println("values not inserted");
                b = false;
            }        // end of else
        }            // end of try
                catch (Exception e) {
            System.out.println("inside SCHEDULEDAO exception at insert" + e);

            // throw e;
        }            // end of catch
                finally {
            if (con != null) {
                try {
                    if (con != null) {
                        con.close();
                    }
                }    // end of try
                        catch (Exception e) {
                    System.out.println("exception at con closed" + e);
                }    // end of catch
            }
        }            // end of finally

        return b;
    }    // end of insert method

    public ArrayList list() throws Exception {
        try {
            System.out.println("inside list method at SCHEDULEDAO");
            con  = getConnect();
            shpl = new SchedulePrepareAL();
            System.out.println(con);

            String query = "SELECT * FROM `schedule` order by Schedule_name,sub_schedule1";

            pstmt = con.prepareStatement(query);
           
            
            rs = pstmt.executeQuery();
            
            al = shpl.prepareArrayList(rs);
            
            rs.close();
            pstmt.close();
            con.close();
        }    // end of try
                catch (Exception e) {
            System.out.println("exception at list method");
        }    // end of catch

        return al;
    }    // end of list

    public ArrayList listForAcHead() throws Exception {
        try {
            System.out.println("inside list method at SCHEDULEDAO");
            con  = getConnect();
            shpl = new SchedulePrepareAL();
            System.out.println(con);

            String query = "SELECT sub_schedule1 FROM `schedule` union " + "SELECT sub_schedule2 FROM `schedule` "
                           + "where Schedule_Status=1";

            System.out.println("--->@@@" + query);
            pstmt = con.prepareStatement(query);
            rs    = pstmt.executeQuery();
            alist = shpl.prepareAcHeadList(rs);
            rs.close();
            pstmt.close();
            con.close();
        }    // end of try
                catch (Exception e) {
            System.out.println("exception at list method");
        }    // end of catch

        return alist;
    }    // end of list

    public ArrayList editlist(String scheduleno) throws Exception {
        try {
            con = getConnect();
            System.out.println("sno is  ..........." + scheduleno);

            // System.out.println(con);
            String query = "select * from schedule where Schedule_No =?";

            // System.out.println("AFTER THE QUERY "+query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            pstmt.setString(1, scheduleno);
            rs = pstmt.executeQuery();
            al = shpl.prepareArrayList(rs);
            rs.close();
            pstmt.close();
            con.close();
        }    // end of try
                catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }    // end of editlist method

    public boolean update(ScheduleBean shb) throws Exception {
        try {
            con = getConnect();
            con.setAutoCommit(false);

            // System.out.println("con at upadate"+con);
            String sno, sname, fname, sdob, sphoneno, smailid, spreseaddr, spermaaddr, smarks, scategory;

            System.out.println("at  Update method in SCHEDULE DAO");

            String ss =
                "update schedule set Schedule_Name=?,Sub_Schedule1=?,Sub_Schedule2=?,Sub_Schedule3=?,Description=?,CreatedBy=?,CreatedDt=? where Schedule_No=?";

            pstmt = con.prepareStatement(ss);
            System.out.println("pstmt at update method" + pstmt);

            // System.out.println("conn and pstmt is created");
            String scheduleno   = shb.getSchedule_No();
            String schedulename = shb.getSchedule_Name();
            String sub1         = shb.getSub_Schedule1();
            String sub2         = shb.getSub_Schedule2();
            String desc         = shb.getDescription();
            String cby          = shb.getCreatedBy();
            String cdt          = shb.getCreatedDt();

            pstmt.setString(1, schedulename);
            pstmt.setString(2, sub1);
            pstmt.setString(3, sub2);
            pstmt.setString(4, desc);
            pstmt.setString(5, cby);
            pstmt.setString(6, cdt);
            pstmt.setString(7, scheduleno);

            // System.out.println("BEFORE EXECUTEUPDATE");
            i = pstmt.executeUpdate();
            con.commit();
            pstmt.close();
            con.close();

            // System.out.println("i value is ......................................."+i);
            if (i == 1) {

                // System.out.println("values are updated......................");;
                b1 = true;
            }    // end of if
                    else {

                // System.out.println("values are not updated....................");
                b1 = false;
            }    // end of else                                           // con.close();
        }        // end of try
                catch (Exception e) {
            System.out.println("INSIDE SCHEDULEDAO EXCEPTION AT UPDATE" + e);
        }        // end of catch
                finally {
            try {
                if (con != null) {
                    con.close();
                }
            }    // end of try
                    catch (Exception e) {
                System.out.println("EXCEPTION AT CON CLOSED" + e);
            }    // end of catch
        }        // end of finally

        return b1;
    }    // end of update method

    public int delete(String scheduleno) throws Exception {
        try {
            System.out.println("Schedule Number is  ..........." + scheduleno);
            con = getConnect();
            con.setAutoCommit(false);

            String query = "update schedule set Schedule_Status=0 where Schedule_No=?";

            pstmt = con.prepareStatement(query);

            // System.out.println("pstmt..............................."+pstmt);
            // System.out.println("city code is(ccode) ..........." +ccode);
            pstmt.setString(1, scheduleno);
            count = pstmt.executeUpdate();

            // System.out.println("COUNT IS ......"+count);
            con.commit();
            pstmt.close();
            con.close();

            // System.out.println("inside delete()    \n");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("exception at delete");
        }                                     // end of catch
                finally {
            try {
                if (con != null) {
                    con.close();
                }
            }                                 // end of try
                    catch (Exception e) {}    // end of catch
        }                                     // end of finally

        return this.count;
    }    // end of delete method

    /* ...........................................max value method................................................................. */
    public String maxValue() throws Exception {
        int    p = 0;
        String k = "";

        try {
            con = getConnect();

            String query = "select count(*) from schedule ";

            pstmt = con.prepareStatement(query);
            rs    = pstmt.executeQuery();

//          System.out.println("Rs  val"+rs);
            if (rs.next()) {
                p = rs.getInt(1) + 1;

//              System.out.println("p VALUE IS ....................."+p);
            }    // end of if

            k = new Integer(p).toString();

//          System.out.println("k VALUE IS .............................."+k);
            rs.close();
            pstmt.close();
            con.close();
        }    // end of try
                catch (Exception e) {
            System.out.println("Exception at MAXmethod" + e);
        }    // end of catch

        return k;
    }

    public ArrayList getlist(String schedulena) throws Exception {
        try {
            con = getConnect();
            System.out.println("sno is  ..........." + schedulena);

            // System.out.println(con);
            // String query = "select * from schedule where Schedule_Name =?";
            String query = "SELECT sub_schedule1 FROM `schedule` " + "where Schedule_Status=1 and Schedule_Name ='"
                           + schedulena + "' union " + "SELECT sub_schedule2 FROM `schedule` "
                           + "where Schedule_Status=1 and Schedule_Name ='" + schedulena + "'";

            System.out.println("AFTER THE QUERY " + query);
            pstmt = con.prepareStatement(query);

            // pstmt.setString(1, schedulena);
            rs = pstmt.executeQuery();
            al = shpl.prepareAcHeadList(rs);
            rs.close();
            pstmt.close();
            con.close();
        }    // end of try
                catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }

    
}    // end of ScheduleDao